﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using HZ.MBSM.DBUtility;
using System.Text.RegularExpressions;

public partial class Manage_ProductcodeUpdate : System.Web.UI.Page {
    private string fp, fp_EXT, sheetName;

    private string Productid = "目录编号";
    private int int_Productid = -1;   //目录流水号的列索引
    private string Productcode;
    private int int_Productcode = -1;
    string remark;


    private string dwname;
    private long accountid;
    private Regex pslReg = new Regex("^[0-9]*[1-9][0-9]*$");  //正整数判断
    private Regex jeReg = new Regex("^(\\d+)(\\.\\d{1,2})?$");  //金额判断
    private Regex dateReg = new Regex(@"^((((1[6-9]|[2-9]\d)\d{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\d|3[01]))|(((1[6-9]|[2-9]\d)\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\d|30))|(((1[6-9]|[2-9]\d)\d{2})-0?2-(0?[1-9]|1\d|2[0-8]))|(((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))$");

    protected void Page_Load(object sender, EventArgs e) {
        System.Collections.Generic.List<HZ.MBSM.DAL.AccountType> _AccountType = new System.Collections.Generic.List<HZ.MBSM.DAL.AccountType>();
        _AccountType.Add(HZ.MBSM.DAL.AccountType.enumAttorney);
        if (HZ.MBSM.BLL.Login.CheckAssert(_AccountType)) {
            HZ.MBSM.Model.Account _Account = (HZ.MBSM.Model.Account)Session["Account"];
            if (_Account != null) {
                dwname = _Account.DWName;
                accountid = _Account.AccountId;

            }
        }
    }
    
    /// <summary>
    /// 
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e) {
        //DataTable dtSource= getSourceData();
        ////GridView1.AllowPaging = true;
        ////GridView1.PageSize = 20;
        //this.GvSource.DataSource = dtSource;
        //this.GvSource.DataBind();
    }


    /// <summary>
    /// 获取数据源,绑定数据
    /// </summary>
    /// <returns></returns>
    private void BindSourceData(string FilePathAndName, string childSheet) {
        DataTable dt = new DataTable();
        try {
            using (System.Data.OleDb.OleDbConnection dbConnExl = getConnection(FilePathAndName)) {
                dbConnExl.Open();
                string sConnectSql = ConfigurationManager.ConnectionStrings[0].ToString(); ;
                SqlConnection dbConnSql = new SqlConnection(sConnectSql);
                string sqlExl = "SELECT * FROM [" + childSheet + "]";
                System.Data.OleDb.OleDbDataAdapter objAdapExl = new System.Data.OleDb.OleDbDataAdapter(sqlExl, dbConnExl);
                DataSet objDataSet = new DataSet();
                objAdapExl.Fill(objDataSet);
                dt = objDataSet.Tables[0];
                dbConnExl.Close();
                GvSource.DataSource = dt;
                GvSource.DataBind();
            }
        } catch (Exception ex) {
            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
    }



    /// <summary>
    /// 获取数据源,生成datatable对象
    /// </summary>
    /// <returns></returns>
    private DataTable GetSourceData(string FilePathAndName, string childSheet) {
        DataTable dt = new DataTable();
        try {
            using (System.Data.OleDb.OleDbConnection dbConnExl = getConnection(FilePathAndName)) {
                dbConnExl.Open();
                string sConnectSql = ConfigurationManager.ConnectionStrings[0].ToString(); ;
                SqlConnection dbConnSql = new SqlConnection(sConnectSql);
                string sqlExl = "SELECT * FROM [" + childSheet + "]";
                System.Data.OleDb.OleDbDataAdapter objAdapExl = new System.Data.OleDb.OleDbDataAdapter(sqlExl, dbConnExl);
                DataSet objDataSet = new DataSet();
                objAdapExl.Fill(objDataSet);
                dt = objDataSet.Tables[0];
                dbConnExl.Close();
                return dt;
            }
        } catch (Exception ex) {
            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
        return dt;
    }

    /// <summary>
    /// 获取源文件的子表
    /// </summary>
    /// <returns></returns>
    private void BindSourceSheet(string FilePathAndName) {
        try {
            using (System.Data.OleDb.OleDbConnection MyConn = getConnection(FilePathAndName)) {
                MyConn.Open();
                DataTable schemaTable = MyConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                DDLsheet.Items.Clear();
                for (int i = 0; i < schemaTable.Rows.Count; i++) {
                    string TableName = schemaTable.Rows[i]["TABLE_NAME"].ToString().Trim();
                    if (TableName.Substring(TableName.Length - 1, 1) != "_") {
                        this.DDLsheet.Items.Add(TableName);
                    }
                }
                DDLsheet.Items.Insert(0, "");
                DDLsheet.SelectedIndex = 0;
            }
        } catch (Exception ex) {
            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
    }


    /// <summary>
    /// 获取连接对象
    /// </summary>
    /// <param name="FilePathAndName"></param>
    /// <returns></returns>
    private System.Data.OleDb.OleDbConnection getConnection(string FilePathAndName) {
        String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePathAndName + ";Extended Properties=Excel 8.0;";
        System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strConn);
        return Conn;
    }


    /// <summary>
    /// 下一步

    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Wizard1_NextButtonClick(object sender, WizardNavigationEventArgs e) {
        labmess0.Visible = false;
        labmess1.Visible = false;
        labmess2.Visible = false;
        labmess3.Visible = false;



        string mess = "";
        try {
            switch (e.CurrentStepIndex) {
                case 0:  //打开文件,判断是否是excel文件
                    //if (ViewState["fp"] != null)
                    //{
                    //    this.filepath.Attributes.Add("value", ViewState["fp"].ToString());
                    //}
                    mess = CheckSourceType();
                    if (mess != "") {
                        labmess0.Visible = true;
                        labmess0.Text = mess;
                        e.Cancel = true;
                    } else {
                        //上传文件
                        DateTime dt = new DateTime();
                        dt = DateTime.Now;
                        Random rnd = new Random(dt.Millisecond);

                        delSourceExcel();   //删除之前上传的excel文件

                        fp = Server.MapPath("../UploadFile/") + accountid + "product" + rnd.Next(10000) + ".xls";
                        filepath.SaveAs(fp);

                        ViewState["fp"] = fp;
                        BindSourceSheet(ViewState["fp"].ToString());
                    }
                    break;
                case 1:  //选择子表,判断用户是否选择了子表

                    mess = CheckChoiceSheet();
                    if (mess != "") {
                        labmess1.Visible = true;
                        labmess1.Text = mess;
                        e.Cancel = true;
                    } else {
                        ViewState["sheetName"] = sheetName;
                        BindSourceData(ViewState["fp"].ToString(), ViewState["sheetName"].ToString());
                    }
                    break;
                case 2:  //打开子表,判断文件是否符合要求
                    BindSourceData(ViewState["fp"].ToString(), ViewState["sheetName"].ToString());
                    mess = CheckSourceColumns();
                    if (mess != "") {
                        labmess2.Visible = true;
                        labmess2.Text = mess;
                        e.Cancel = true;
                    } else {
                        //txtPlanName.Text = dwname + System.DateTime.Now.ToShortDateString() + "采购单";
                        //  txtRemarks.Text = "接口管理导入";
                    }

                    break;
            }
        } catch (Exception ex) {
            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
    }


    /// <summary>
    /// 点击导航栏按钮

    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Wizard1_SideBarButtonClick(object sender, WizardNavigationEventArgs e) {

        Wizard1_NextButtonClick(sender, e);
    }

    /// <summary>
    /// 开始导入,判断采购单名称和时间是否有效
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e) {

        startImport();

    }

    /// <summary>
    /// 检查源文件的格式,是否是excel文件
    /// </summary>
    /// <returns></returns>
    private string CheckSourceType() {
        string mess = "";
        fp = this.filepath.PostedFile.FileName;
        if (fp == "") {
            mess = "对不起,请您选择要打开的文件!";
            return mess;
        }
        int ipost = fp.LastIndexOf(".");
        if (ipost == -1) {
            fp_EXT = "";
        } else {
            fp_EXT = fp.Substring(ipost, fp.Length - ipost);
        }
        if (fp_EXT != ".xls") {
            mess = "对不起,您只能打开Excel格式的文件!";
        }
        return mess;
    }


    /// <summary>
    /// 检查用户是否选择了excel文件的子表

    /// </summary>
    /// <returns></returns>
    private string CheckChoiceSheet() {
        string mess = "";
        if (DDLsheet.Items.Count == 0) {
            mess = "对不起,请您选择要打开的文件!";
        }
        sheetName = DDLsheet.Items[DDLsheet.SelectedIndex].Text.Trim();

        if (sheetName == "") {
            mess = "对不起,请选择子表!";
        }
        return mess;
    }


    /// <summary>
    /// 检查采购单源文件是否符合规定(固定格式)
    /// </summary>
    /// <returns></returns>
    private string CheckSourceColumns() {
        string mess = "";
        DataTable dt = (DataTable)GvSource.DataSource;
        Productid = "目录编号";
        int_Productid = -1;   //药品流水号的列索引


        Productcode = "品目编号";
        int_Productcode = -1; //配送企业编码的列索引


      
        int colNum = dt.Columns.Count;
        if (colNum == 0) {
            mess = "对不起,您选择的文件是空的!";
            return mess;
        }

        for (int i = 0; i < colNum; i++) {
            if (dt.Columns[i].ColumnName.Trim() == Productid) {
                if (int_Productid == -1) {
                    int_Productid = i;
                    continue;
                } else {
                    mess = "对不起,您选择的文件中表头[目录编号]不唯一!";
                    return mess;
                }
            }

            if (dt.Columns[i].ColumnName.Trim() == Productcode) {
                if (int_Productcode == -1) {
                    int_Productcode = i;
                    continue;
                } else {
                    mess = "对不起,您选择的文件中表头[品目编号]不唯一!";
                    return mess;
                }
            }

           
        }


        if (int_Productid == -1) {
            mess = "对不起,您选择的文件中,表头[目录编号]不存在!";
            return mess;
        }

        if (int_Productcode == -1) {
            mess = "对不起,您选择的文件中,表头[品目编号]不存在!";
            return mess;

        }

        ViewState["int_Productid"] = int_Productid;
        ViewState["int_Productcode"] = int_Productcode;
          return mess;
    }



    /// <summary>
    /// 删除上传的excel文件
    /// </summary>
    private void delSourceExcel() {
        if (ViewState["fp"] == null) { return; }
        if (System.IO.File.Exists(ViewState["fp"].ToString())) {
            System.IO.File.Delete(ViewState["fp"].ToString());
        }
    }

    /// <summary>
    /// 开始导入

    /// </summary>
    private void startImport() {
        string planname = "";// txtPlanName.Text.Trim();
        int planid = -1;
        int succROW = 0;                      //导入成功记录数

        try {
            DataTable dtSource = GetSourceData(ViewState["fp"].ToString(), ViewState["sheetName"].ToString());

            delSourceExcel();

            int totalROW = dtSource.Rows.Count;   //源文件总记录数

             int int_Productid = int.Parse(ViewState["int_Productid"].ToString());
            int int_Productcode = int.Parse(ViewState["int_Productcode"].ToString());
           
            Db db;
            string sql = "";
            foreach (DataRow dr in dtSource.Rows) {
                string str_Productid = dr[int_Productid].ToString().Trim();     //药品流水号  正整数
                string str_Productcode = dr[int_Productcode].ToString().Trim(); // 
                string str_SupplyGoodsId = "";
                if (pslReg.IsMatch(str_Productid)) {
                    sql = "select Productid  as cum from product where Productid=" + SysFun.SqlStr(str_Productid) ;
                    db = new Db();
                    System.Data.IDataReader drr = db.DataReader(sql);
                    bool isImport = true;
                    if (drr.Read())   //导入的数据属于Product目录
                    {
                        if (drr["cum"].ToString() == "")   //无此记录
                        {
                            isImport = false;
                            //this.labmess4.Text = "更新,已经全部成功!,总计更新 " + succROW + " 条记录！"
                        } 
                        else 
                        {
                             HZ.MBSM.Model.Account _Account = (HZ.MBSM.Model.Account)Session["Account"];
                             Db dbq = new Db();
                        string sqlss = "update product set productcode='" + str_Productcode + "',remark='" + _Account.AccountName + "," + System.DateTime.Now.ToString() + "' where productid=" + str_Productid;
                        int succ = dbq.Command(sqlss, 120);
                        dbq.Close();
                       
                        if (succ == 1)  //导入成功,删除此药品
                        {
                            succROW++;
                            dr.Delete();
                        }
                        }
                    } 

                    drr.Close();
                    db.Close();

                }
            }

            labmess4.Visible = true;
            if (succROW == totalROW) {
                this.labmess4.Text = "更新,已经全部成功!,总计更新 " + succROW + " 条记录！";

            } else {
                if (succROW != 0) {
                    this.labmess4.Text = "文件中有 " + succROW + " 条更新成功, " + (totalROW - succROW) + " 条导入失败(列入下表)";
                } else {
                    this.labmess4.Text = "文件导入失败!下表列出未能更新的药品信息.";
                }
                this.GvNoImport.DataSource = dtSource;
                GvNoImport.DataBind();
            }

        } catch (Exception ex) {

            Response.Write("<script>alert(\"错误!\\n   " + EncodeHtml(ex.Message) + "\");window.history.back();</script>");
            Response.End();
        }
    }




    /// <summary>
    /// 替换错误提示中的特殊字符
    /// </summary>
    /// <param name="mess"></param>
    /// <returns></returns>
    private string EncodeHtml(string mess) {
        string[] special = new string[] { "\"", "'", "\n", "\\", "\r\n" };
        foreach (string s in special) {
            mess = mess.Replace(s, "");
        }
        return mess;
    }
    protected void btnExcel_Click(object sender, EventArgs e) {
        downLoadUnImport(GvNoImport);
    }

    private void downLoadUnImport(GridView gv) {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
        Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("未更新的明细.xls"));
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        gv.RenderControl(oHtmlTextWriter);

        // 把文件流发送到客户端 
        Response.Write(oStringWriter.ToString());
        // 停止页面的执行

        Response.End();
    }


    public override void VerifyRenderingInServerForm(Control control) {
        //OverRide　为了使导出成Excel可行！

    }
}

